/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package com.marlboroug.articlesbyauthors;

import com.marlboroug.entity.Articlesbyauthors;
import com.marlboroug.entity.Authors;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.TypedQuery;


@Stateless
public class ArticlesByAuthorsManager implements ArticlesByAuthorsRemote {
    
    @PersistenceContext(unitName = "Marlboroug-ejbPU")
    EntityManager em;
    
    /** Permet de créer une relation entre un article et un auteur
     * 
     * @param articlesByAuthor Envoie d'un objet de type articlesByAuthor
     * @return Un boolean pour confirmer la création
     */
    @Override
    public boolean create (Articlesbyauthors articlesByAuthor) {       
        em.persist(articlesByAuthor);
        return true;
    }
    
    /** Permet de supprimer une relation entre un article et un auteur
     * 
     * @param ID_articles_by_authors Envoie un ID 
     * @return Un boolean pour confirmer la suppression
     */
    @Override
    public boolean delete (Long ID_articles_by_authors) {
        Articlesbyauthors articlesByAuthor = em.find(Articlesbyauthors.class, ID_articles_by_authors);
        em.remove(articlesByAuthor);
        Articlesbyauthors temp = find(ID_articles_by_authors);
        return temp == null;
    }
    
    /** Permet de renvoyer un objet articlesByAuthor en fonction d'un ID
     * 
     * @param ID_articles_by_authors Envoie un ID
     * @return Un objet articlesByAuthor
     */
    @Override
    public Articlesbyauthors find (Long ID_articles_by_authors) {
        Articlesbyauthors articlesByAuthor = em.find(Articlesbyauthors.class, ID_articles_by_authors);
        return articlesByAuthor;
    }
    
    /** Permet de retourner un objet auteur en fonction d'un ID
     * 
     * @param ID_authors Un ID d'un auteur
     * @return Un objet auteur
     */
    @Override
    public Authors findAuthors (Long ID_authors) {
        Authors author = em.find(Authors.class, ID_authors);
        return author;
    }
    
    /** Permet de retourner une liste des relations entre un article et un auteur en fonction d'un auteur
     * 
     * @param ID_authors Un ID d'un auteur
     * @return Une liste d'objets de type articlesByAuthors
     */
    @Override
    public List<Articlesbyauthors> ArticlesFindAuthor (Long ID_authors) { //Sans limit
        String q = "SELECT a FROM Articlesbyauthors a INNER JOIN Authors c WHERE :object = a.idAuthors AND c.iDauthors = :id"; //SELECT * FROM Articles INNER JOIN Categories WHERE Categories.ID_categories = Articles.ID_categories AND Categories.ID_categories = 9
        TypedQuery<Articlesbyauthors> query = em.createQuery(q, Articlesbyauthors.class);  
        query.setParameter("object", findAuthors(Long.valueOf(ID_authors)));
        query.setParameter("id", ID_authors);
        List<Articlesbyauthors> articleByAuthors = query.getResultList();
        return articleByAuthors;
    }
    
    /** Permet de retourner une liste des 10 articles les plus vendus avec les auteurs, catégories et éditeurs associès
     * 
     * @return Une liste d'articles
     */
    @Override //Liste les artciles et ce qui si ratache en partant de la table Articlesbyauthors car on a accès a toutes les clef etrangères
    public List<Articlesbyauthors> findArticlesIndex () {
        /*
            SELECT *
            FROM Articles_by_authors 
            INNER JOIN Authors ON Articles_by_authors.id_authors = Authors.ID_authors 
            INNER JOIN Articles ON Articles.ID_articles = Articles_by_authors.id_articles 
            INNER JOIN Editors ON Articles.id_editors = Editors.ID_editors
            INNER JOIN Categories ON Articles.id_categories = Categories.ID_categories     
            ORDER BY Articles.sold DESC
            LIMIT 10
        */

        String q = "SELECT aba "
                + "FROM Articlesbyauthors aba "
                + "INNER JOIN Authors au "
                + "ON aba.idAuthors.iDauthors = au.iDauthors "
                + "INNER JOIN Articles ar "
                + "ON aba.idArticles.iDarticles = ar.iDarticles "
                + "INNER JOIN Editors ed "
                + "ON ar.idEditors.iDeditors = ed.iDeditors "
                + "INNER JOIN Categories ca "
                + "ON ar.idCategories.iDcategories = ca.iDcategories "
                + "ORDER BY ar.sold DESC";
        TypedQuery<Articlesbyauthors> query = em.createQuery(q, Articlesbyauthors.class);
        query.setMaxResults(10);
        List<Articlesbyauthors> article = query.getResultList();
        return article;
    }
    
    /** Permet de retourner une liste d'articles avec les auteurs, catégories et éditeurs associès en fonction d'une categories
     * 
     * @param ID_categories Un ID d'une categorie
     * @return Une liste d'articles
     */
    @Override
    public List<Articlesbyauthors> ArticlesFindCategory (Long ID_categories) { //Sans limit
        /*
        SELECT *
        FROM Articles_by_authors 
        INNER JOIN Authors ON Articles_by_authors.id_authors = Authors.ID_authors 
        INNER JOIN Articles ON Articles.ID_articles = Articles_by_authors.id_articles 
        INNER JOIN Editors ON Articles.id_editors = Editors.ID_editors
        INNER JOIN Categories ON Articles.id_categories = Categories.ID_categories
        WHERE Categories.ID_categories = ?
        ORDER BY Articles.name ASC 
         */
        String q = "SELECT aba "
                + "FROM Articlesbyauthors aba "
                + "INNER JOIN Authors au "
                + "ON aba.idAuthors.iDauthors = au.iDauthors "
                + "INNER JOIN Articles ar "
                + "ON aba.idArticles.iDarticles = ar.iDarticles "
                + "INNER JOIN Editors ed "
                + "ON ar.idEditors.iDeditors = ed.iDeditors "
                + "INNER JOIN Categories ca "
                + "ON ar.idCategories.iDcategories = ca.iDcategories "
                + "WHERE ca.iDcategories = :id "
                + "ORDER BY ar.name ASC";
        TypedQuery<Articlesbyauthors> query = em.createQuery(q, Articlesbyauthors.class);  
        query.setParameter("id", ID_categories);
        List<Articlesbyauthors> article = query.getResultList();
        return article;
    }
    
    /** Permet de retourner une liste d'articles avec les auteurs, catégories et éditeurs associès en fonction d'un mot clés
     * 
     * @param keyWord Un mot clés
     * @return Une liste d'articles
     */
    @Override
    public List<Articlesbyauthors> ArticlesFindCategoryWithKeyWord (String keyWord) { //Sans limit
        /*
        SELECT *
        FROM Articles_by_authors 
        INNER JOIN Authors ON Articles_by_authors.id_authors = Authors.ID_authors 
        INNER JOIN Articles ON Articles.ID_articles = Articles_by_authors.id_articles
        INNER JOIN Editors ON Articles.id_editors = Editors.ID_editors
        INNER JOIN Categories ON Articles.id_categories = Categories.ID_categories
        WHERE Articles.`name` LIKE "%?%"
        ORDER BY Articles.name ASC 
         */
        
        String q = "SELECT aba "
                + "FROM Articlesbyauthors aba "
                + "INNER JOIN Authors au "
                + "ON aba.idAuthors.iDauthors = au.iDauthors "
                + "INNER JOIN Articles ar "
                + "ON aba.idArticles.iDarticles = ar.iDarticles "
                + "INNER JOIN Editors ed "
                + "ON ar.idEditors.iDeditors = ed.iDeditors "
                + "INNER JOIN Categories ca "
                + "ON ar.idCategories.iDcategories = ca.iDcategories "
                + "WHERE ar.name LIKE :keyWord "
                + "ORDER BY ar.name ASC";        
        TypedQuery<Articlesbyauthors> query = em.createQuery(q, Articlesbyauthors.class);  
        keyWord = "%" + keyWord + "%";
        query.setParameter("keyWord", keyWord);
        List<Articlesbyauthors> article = query.getResultList();
        return article;
    }
   
    /** Permet de renvoyer une liste de l'ensemble des articles avec les auteurs, catégories et éditeurs associès
     * 
     * @return Une liste d'articles
     */
    @Override
    public List<Articlesbyauthors> ArticlesFindAll (){
        /*
        SELECT *
        FROM Articles_by_authors 
        INNER JOIN Authors ON Articles_by_authors.id_authors = Authors.ID_authors 
        INNER JOIN Articles ON Articles.ID_articles = Articles_by_authors.id_articles
        INNER JOIN Editors ON Articles.id_editors = Editors.ID_editors
        INNER JOIN Categories ON Articles.id_categories = Categories.ID_categories
        ORDER BY Articles.name ASC 
         */
        String q = "SELECT aba "
                + "FROM Articlesbyauthors aba "
                + "INNER JOIN Authors au "
                + "ON aba.idAuthors.iDauthors = au.iDauthors "
                + "INNER JOIN Articles ar "
                + "ON aba.idArticles.iDarticles = ar.iDarticles "
                + "INNER JOIN Editors ed "
                + "ON ar.idEditors.iDeditors = ed.iDeditors "
                + "INNER JOIN Categories ca "
                + "ON ar.idCategories.iDcategories = ca.iDcategories "
                + "ORDER BY ar.name ASC";
        TypedQuery<Articlesbyauthors> query = em.createQuery(q, Articlesbyauthors.class);  
        List<Articlesbyauthors> article = query.getResultList();
        return article;
    }
}
